# coding=utf8

import sys

from openpyxl.reader.excel import load_workbook
from openpyxl.styles import Font, Border, Side
import schema.xlsx as xlsx

reload(sys)
sys.setdefaultencoding('utf-8')

COLUMNS = ['A', 'B', 'C', 'D',
           'E', 'F', 'G', 'H',
           'I', 'J', 'K', 'L',
           'M', 'N', 'O', 'P',
           'Q', 'R', 'S', 'T',
           'U', 'V', 'W', 'X',
           'Y', 'Z'
           ]


def auto_size(ws):
    dims = {}
    for row in ws.rows:
        for cell in row:
            if cell.value:
                dims[cell.column] = max((dims.get(cell.column, 0), len(cell.value)))
    for col, value in dims.items():
        if value < 50:
            ws.column_dimensions[col].width = value * 1.2 + 3
        else:
            ws.column_dimensions[col].width = 50


def process(work_book):
    wb = load_workbook(filename=work_book)
    for ws in wb.worksheets:
        print ws.title
        process_work_sheet(ws)
        auto_size(ws)
    wb.save(work_book)


def process_work_sheet(ws):
    font = Font(name=u'宋体', size=12)
    border = Border(left=Side(style='thin'),
                    right=Side(style='thin'),
                    top=Side(style='thin'),
                    bottom=Side(style='thin'))
    ws.sheet_view.zoomScale = 100

    for c in range(1, ws.max_column + 1):
        for r in range(1, ws.max_row + 1):
            ws.cell(row=r, column=c).font = font
            cell = ws.cell(row=r, column=c)
            cell.border = border
            line = ws.cell(row=r, column=c).value
            if line:
                ws.cell(row=r, column=c).value = str(line).replace(u'\u3000', ' ').strip()
            else:
                ws.cell(row=r, column=c).value = ''
    ws.print_area = 'A1:%s%d' % (xlsx.COLUMNS[ws.max_column - 1], ws.max_row)
